package Controllers;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Observable;
import java.util.Observer;

import GUI.MarketingEmployeeGUI;
import Server.RsWraper;
import Server.Tuple;


/**
 * The Class ComputeRanksController.
 */
public class ComputeRanksController implements Observer{

	/** The marketing employee gui. */
	private MarketingEmployeeGUI marketingEmployeeGui;
	
	/** The tuples. */
	private ArrayList<Tuple> tuples;
	
	/** of all the customers, one had bought the most amount of products.
	 * this variable stores that maximum amount, and we will use it to generate ranks
	 *  */
	private double maxTotalQuantity;
	
	/** of all the customers, one had spent the most amount of money on buying different products.
	 * this variable stores that maximum value, and we will use it to generate ranks
	 *  */
	private double maxTotalSpending;
	
	/** The operation.
	 * by this variable the update function can know in what stage the rank computation is, and what it should do next
	 *  */
	private int operation=0;

	/**
	 * Instantiates a new compute ranks controller.
	 *
	 * @param marketingEmployeeGui the marketing employee gui
	 */
	public ComputeRanksController(MarketingEmployeeGUI marketingEmployeeGui) {
		this.marketingEmployeeGui=marketingEmployeeGui;
		ClientController.setObserver(this);
	}

	/**
	 * Calculate ranks.
	 * Begin by calculating the interest levels.
	 */
	public void calculateRanks() {
		computeInterestLevel();
	}

	/**
	 * Compute interest level.
	 * Begin by sending a query to the server, that returns a table with the number of approves and declines for each product and customer that was offered that product
	 */
	public void computeInterestLevel() {
		String query=""
				+ "select v1.cid,v1.pid,v1.approval,v2.decline from\n"
				+ "(select tab1.cid as cid, tab1.pid as pid,(select count(*) from customersresponsestooffer as tab2 where tab2.customerResponse='YES' and tab2.cid=tab1.cid and tab2.pid=tab1.pid) as approval from customersresponsestooffer as tab1\n"
				+ "group by cid,pid) as v1,\n"
				+ "(select tab1.cid as cid, tab1.pid as pid,(select count(*) from customersresponsestooffer as tab2 where tab2.customerResponse='NO' and tab2.cid=tab1.cid and tab2.pid=tab1.pid) as decline from customersresponsestooffer as tab1\n"
				+ "group by cid,pid) as v2\n"
				+ "where v1.cid=v2.cid and v1.pid=v2.pid";
		try {
			ClientController.observableClient.sendToServer(query);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}

	/**
	 * Compute importance rank.
	 * Begin by sending a query to the server and will return a table with the values of variables maxTotalQuantity and maxTotalSpending (read their purpose at the beginning)
	 */
	public void computeImportanceRank() {
		String query=""
				+ "select max(v2.totalQuantity) as maxTotalQuantity, max(v2.totalSpending) as maxTotalSpending\n"
				+ "from\n"
				+ "(select v1.cid,sum(v1.quantity) as totalQuantity,sum(v1.price) as totalSpending\n"
				+ "from\n"
				+ "(SELECT responses.*, products.price*responses.quantity as price FROM customersresponsestooffer as responses,products as products\n"
				+ "where responses.pid=products.pid and responses.customerResponse='YES') as v1\n"
				+ "group by v1.cid) as v2\n";
		try {
			ClientController.observableClient.sendToServer(query);
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}

	/**
	 * when operation=0 this function receives the table that I explained about in function computeInterestLevel. Then it sends a query to write the interest levels
	 * to the authorization table. after that operation=1 and this means that now the server should return a message to about the success/failure of our previous query.
	 * if the message was a success message, then we call computeImportanceRank() to compute the importance rank.
	 * computeImportanceRank() sends a query to the server, that returns to us the values of variables maxTotalQuantity and maxTotalSpending.
	 * we receive the values of these two variables in the update function  when operation=2.
	 * then function update sends another query to get a table that has for each customer, how how much money did he/she spent so far, and what is the total quantity of products that he/she bought so far.
	 * we get this table in the function update when operation=3.
	 * from the last table, and the values of maxTotalQuantity,maxTotalSpending, we calculate in function update, using an algorithm, for each customer he's/she's importance rank and we send last query to server 
	 * to update these ranks in table customers. after that when operation=4, we get a message from the regarding if our last query was executed successfully or not, and we show an appropriate message accordingly.
	 *
	 * @param o the o
	 * @param arg the arg
	 */
	@Override
	public void update(Observable o, Object arg) {
		// TODO Auto-generated method stub
		if(operation==0)
		{
			if(arg.toString().equals("error") || arg.toString().equals("empty query") || arg.toString().equals("not read/write sql exception"))
				marketingEmployeeGui.displayWarningMessage("Error - the computation was not completed");
			else
				if(arg.toString().equals("empty query"))
					marketingEmployeeGui.displayMessage("The computation was completed successfully");
				else{
					int interestLevel;
					int approvalNum;
					int declineNum;
					tuples=new ArrayList<Tuple>();
					tuples.addAll(((RsWraper)arg).getRows());
					String query="UPDATE authorizations SET interestLevel=\nCASE\n";
					for(int i=0;i<tuples.size();i++) {
						approvalNum=Integer.parseInt(tuples.get(i).get("APPROVAL"));
						declineNum=Integer.parseInt(tuples.get(i).get("DECLINE"));
						if(approvalNum==declineNum && approvalNum==0)
							interestLevel=1;
						else
							if(approvalNum <= declineNum)
								interestLevel=1+(int)Math.round(4*( ((double)approvalNum) / declineNum));	// we add 1 so that range is between 1 to 5
							else
								if(approvalNum-declineNum>=2*declineNum)
									interestLevel=10;
								else
									interestLevel=5+(int)Math.round(5*((double)(approvalNum-declineNum))/(2*declineNum));
						query+="WHEN cid='"+tuples.get(i).get("CID")+"' AND pid="+tuples.get(i).get("PID")+" THEN '"+interestLevel+"'\n";
					}
					query+="ELSE interestLevel\nEND";
					operation=1;
					try {
						ClientController.observableClient.sendToServer(query);
					} catch (IOException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				}
		}
		else
		{
			if(operation==1) {
				if(arg.toString().equals("error") || arg.toString().equals("empty query") || arg.toString().equals("not read/write sql exception"))
					marketingEmployeeGui.displayWarningMessage("Error - the computation was not completed");
				else
					if(arg.toString().equals("#Server:Action succeded"))
					{
						operation=2;
						computeImportanceRank();
					}
			}
			else
				if(operation==2) {
					if(arg.toString().equals("error") || arg.toString().equals("not read/write sql exception"))
						marketingEmployeeGui.displayWarningMessage("Error - the computation was not completed");
					else
					{
						String query=""
								+ "select v1.cid,sum(v1.quantity) as totalQuantity,sum(v1.price) as totalSpending\n"
								+ "from\n"
								+ "(SELECT responses.*, products.price*responses.quantity as price FROM customersresponsestooffer as responses,products as products\n"
								+ "where responses.pid=products.pid and responses.customerResponse='YES') as v1\n"
								+ "group by v1.cid";
						tuples=new ArrayList<Tuple>();
						tuples.addAll(((RsWraper)arg).getRows());
						maxTotalQuantity=Double.parseDouble(tuples.get(0).get("MAXTOTALQUANTITY"));
						maxTotalSpending=Double.parseDouble(tuples.get(0).get("MAXTOTALSPENDING"));
						operation=3;
						try {
							ClientController.observableClient.sendToServer(query);
						} catch (IOException e1) {
							// TODO Auto-generated catch block
							e1.printStackTrace();
						}
					}
				}
				else
					if(operation==3) {
						if(arg.toString().equals("error") || arg.toString().equals("empty query") || arg.toString().equals("not read/write sql exception"))
							marketingEmployeeGui.displayWarningMessage("Error - the computation was not completed");
						else
							if(arg.toString().equals("empty query"))
								marketingEmployeeGui.displayMessage("The computation was completed successfully");
							else
							{
								int importanceRank;
								double totalQuantity;
								double totalSpending;
								tuples=new ArrayList<Tuple>();
								tuples.addAll(((RsWraper)arg).getRows());
								String partOfQuery="WHERE cid IN(";
								String query="UPDATE CUSTOMERS\nSET importancerank = CASE cid\n";
								for(int i=0;i<tuples.size();i++) {	//multiple rows inserting
									totalQuantity=Double.parseDouble(tuples.get(i).get("TOTALQUANTITY"));
									totalSpending=Double.parseDouble(tuples.get(i).get("TOTALSPENDING"));
									importanceRank=1 + (int)Math.round( 4.5*(totalQuantity / maxTotalQuantity) + 4.5*(totalSpending / maxTotalSpending));	// because ranks should be from 1 to 10 
									if(i==0)
										partOfQuery+="'"+tuples.get(i).get("CID")+"'";
									else
										partOfQuery+=", '"+tuples.get(i).get("CID")+"'";
									query+="WHEN '"+tuples.get(i).get("CID")+"' THEN '"+importanceRank+"'\n";
								}
								query+="ELSE 1\nEND\n"+partOfQuery+");";
								operation=4;
								try {
									ClientController.observableClient.sendToServer(query);
								} catch (IOException e1) {
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
							}
					}
					else
						if(operation==4) {
							if(arg.toString().equals("error") || arg.toString().equals("empty query") || arg.toString().equals("not read/write sql exception"))
								marketingEmployeeGui.displayWarningMessage("Error - the computation was not completed");
							else
								marketingEmployeeGui.displayMessage("The computation was completed successfully");
						}
		}
	}
}